﻿//-----------------------------------------------------------------------
// <copyright file="SqlUtilities.cs" company="FastNET Services">
//     Copyright (c) 2008-2009. All Rights Reserved.
// </copyright>
// <license>
//     GPLv2
// </license>
// <warranty>
//     THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING
//     BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
//     NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
//     DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
//     OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
// </warranty>
//-----------------------------------------------------------------------
#region Usings

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using GateShift.Tools.DatabaseMigration.Exceptions;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

#endregion

namespace GateShift.Tools.DatabaseMigration.Utilities
{
    /// <summary>
    /// Utility class for processing all SQL related processes
    /// </summary>
    internal static class SqlUtilities
    {
        /// <summary>
        /// Retrieves a list of all SQL Servers and instances that can be found on the network using the SQL Browser system
        /// </summary>
        /// <returns>List of strings of all found instances</returns>
        public static List<string> RetrieveSqlInstances()
        {
            List<string> instances = new List<string>();

            foreach (DataRow row in SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
            {
                string serverName = row["ServerName"] as string;

                if (serverName == Environment.MachineName)
                {
                    serverName = ".";
                }

                instances.Add(string.IsNullOrEmpty(row["InstanceName"] as string)
                              ? string.Format("{0}", serverName)
                              : string.Format("{0}\\{1}", serverName, row["InstanceName"]));
            }

            return instances;
        }

        /// <summary>
        /// Retrieves a list of all SQL Databases that can be found on the given SQL Connection
        /// </summary>
        /// <param name="connectionString">DB Connection string to use during processing</param>
        /// <returns>List of strings of all found databases</returns>
        public static List<string> RetrieveDatabaseList(string connectionString)
        {
            List<string> databases = new List<string>();
            SqlConnectionStringBuilder masterDB = new SqlConnectionStringBuilder(connectionString) { InitialCatalog = "master" };
            SqlCommand cmd = new SqlCommand("EXEC sp_databases", new SqlConnection(masterDB.ConnectionString));

            try
            {
                cmd.Connection.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            switch (reader["DATABASE_NAME"].ToString().ToLower())
                            {
                                case "master":
                                case "model":
                                case "tempdb":
                                    continue;
                                default:
                                    databases.Add(reader["DATABASE_NAME"].ToString());
                                    break;
                            }
                        }
                    }
                }
            }
            catch
            {
                databases.Add("(Unable to retrieve list)");
            }
            finally
            {
                cmd.Connection.Close();
            }

            return databases;
        }

        /// <summary>
        /// Gets the current changeset for the database
        /// </summary>
        /// <param name="connectionString">DB Connection string to use during processing</param>
        /// <returns>Current Changeset Number</returns>
        public static int GetCurrentChangeSet(string connectionString)
        {
            try
            {
                SqlCommand client = new SqlCommand { Connection = new SqlConnection(connectionString) };

                client.Connection.Open();

                client.CommandText = "SELECT TOP 1 [Version].FeatureChangeset FROM [Version] WHERE [Version].FeatureName = 'Database'";

                client.CommandType = CommandType.Text;

                return Convert.ToInt32(client.ExecuteScalar());
            }
            catch
            {
                return -1;
            }
        }

        /// <summary>
        /// Runs the specified batch script against a specified connection string
        /// </summary>
        /// <param name="script">SQL Scripting Text to run</param>
        /// <param name="connectionString">DB Connection string to use during processing</param>
        /// <returns>Boolean whether or not the update succeeded</returns>
        public static bool RunBatch(string script, string connectionString)
        {
            Server server = new Server(new ServerConnection(new SqlConnection(connectionString)));

            try
            {
                if (server.ConnectionContext.ExecuteNonQuery(script) > -1)
                {
                    return true;
                }
            }
            catch (ExecutionFailureException e)
            {
                throw new BatchExecutionException("Failed to run batch script.", e);
            }

            return false;
        }
    }
}